A table comparison transform identifies differences between an input table and a comparison table. The differences are then updated in an output table. Differences are typically identified at the row level. For example, a row in the output table may be flagged with a label, such as INSERT, UPDATE or DELETE.
One table comparison method is a row-by-row select technique that applies a Structured Query Language (SQL) query to the comparison table for every input row from the input table. This option is advantageous when the comparison table is large compared to the number of rows in the input table.
With the cached comparison method, a comparison table is loaded into dynamic memory. In this case, queries to the comparison table access dynamic memory rather than the actual table. This technique is most successful when the comparison table fits into dynamic memory. Therefore, the technique does not scale and is otherwise unavailable for large comparison tables.
Another type of comparison method is based upon sorted input. With this technique, the comparison table is read in the order of the primary key columns. This approach is efficient because it requires only a single read of the comparison table. To exploit this technique, the order of the input data set must match the order of all primary key columns in the comparison table. In other words, the sorted information from the input table must match the sorted information from the comparison table. In addition, the comparison mechanism must be uniform. A problem may arise when an input table from a first database is sorted using a first technique, while a comparison table from a second database is sorted using a second technique. In such an event, the sort sequences may not be identical. Therefore, the resultant comparison operation may be erroneous. Similarly, if the databases use different comparison mechanisms, the comparison operation may be erroneous.
In view of the foregoing, it would be desirable to provide a technique to establish appropriate sorting of input table information and comparison table information to insure an accurate table comparison operation.